package ssi.peeno.managers;

import java.security.PublicKey;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;

import ssi.peeno.model.User;

public class UserManager {
	private SQLManager sql;
	private KeyManager key;
	
	public UserManager(SQLManager sql) {
		this.sql = sql;
	}
	
	public UserManager(SQLManager sql, KeyManager key) {
		this.sql = sql;
		this.key = key;
	}
	
	/**
	 * Add friend relationship between @user and @friend
	 * 
	 * @param user
	 * @param friend
	 */
	public void addFriend(User user, User friend, String status) {
		String query = "INSERT INTO FriendsOf VALUES (NULL," +
				" '" + user.getId() + "'," +
				" '" + friend.getId() + "'," +
				" '" + status + "')" +
				";";
		sql.executeQuery(query);
		query = "SELECT frdID FROM FriendsOf WHERE " +
				"frdUser1 = " + user.getId() + " AND " +
				"frdUser2 = " + friend.getId() + ";";
		ResultSet rs = sql.returnQuery(query);
		int id = -1;
		try {
			while (rs.next()) {
				id = rs.getInt("frdID");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	
	public int getFriendRequestCount(User user) {
		String username = user.getUsername();
		String query = "SELECT COUNT(*) AS requestCount FROM FriendsOf A JOIN User B ON " +
				"A.frdUser2 = B.usrID " +
				"WHERE B.usrUsername = '" + username + "' AND " +
				"frdStatus = 'REQUEST'";
		ResultSet rs = sql.returnQuery(query);
		try {
			rs.next();
			return rs.getInt("requestCount");
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}
	}
	
	public User[] getRemovedMe(User user) {
		int id = user.getId();
		String query = "SELECT * FROM FriendsOf INNER JOIN User " +
				"ON usrID = frdUser2 " +
				"WHERE frdUser1 = " + id + " AND " +
				"frdStatus = 'REMOVED'";
		ResultSet rs = sql.returnQuery(query);
		ArrayList<User> users = new ArrayList<User>();
		try {
			while (rs.next()) {
				User userTemp = new User();
				userTemp.setEmail(rs.getString("usrEmail"));
				userTemp.setId(rs.getInt("usrID"));
				userTemp.setUsername(rs.getString("usrUsername"));
				users.add(userTemp);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
		query = "DELETE FROM FriendsOf WHERE frdUser1 = " + id + " AND frdStatus = 'REMOVED'";
		ConfigManager.log(query);
		sql.executeQuery(query);
		return contructArray(users);
	}

	private User[] contructArray(ArrayList<User> users) {
		User[] ret = new User[users.size()];
		for (int i=0; i<users.size(); ++i)
			ret[i] = users.get(i);
		return ret;
	}

	public boolean existsEmail(String email) {
		String query = "SELECT * FROM User " +
				"WHERE usrEmail = '" + email + "';";
		ResultSet rs = sql.returnQuery(query);
		try {
			return rs.next() == true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
	
	public boolean existsUsername(String username) {
		String query = "SELECT * FROM User WHERE usrUsername = '" + username + "';";
		ResultSet rs = sql.returnQuery(query);
		try {
			return rs.next() == true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public User loadByID(int id) {
		String query = "SELECT * FROM User WHERE usrID = " + id + ";";
		ResultSet rs = sql.returnQuery(query);
		try {
			rs.next();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return buildUser(rs);
	}
	
	public User loadByUsername(String username) {
		String query = "SELECT * FROM User WHERE usrUsername = '" + username + "';";
		ResultSet rs = sql.returnQuery(query);
		try {
			rs.next();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return buildUser(rs);
	}
	
	/**
	 * Remove friend relationship between @user and @friend
	 * 
	 * @param self
	 * @param friend
	 */
	public void removeFriend(User self, String friend) {
		User fr = loadByUsername(friend);
		String query = "DELETE FROM FriendsOf " +
				"WHERE frdUser1 = " + self.getId() +
				" AND frdUser2 = " + fr.getId() + ";" ;
		sql.executeQuery(query);
		query = "DELETE FROM FriendsOf " +
			"WHERE frdUser1 = " + fr.getId() +
			" AND frdUser2 = " + self.getId() + ";" ;
		sql.executeQuery(query);
	}
	
	/**
	 * Refuse a friendship request
	 * 
	 * @param self
	 * @param friend
	 */
	public void refuseFriendShip(User self, String friend) {
		User fr = loadByUsername(friend);
		String query = "DELETE FROM FriendsOf " +
				"WHERE frdUser1 = " + fr.getId() +
				" AND frdUser2 = " + self.getId() + ";";
		sql.executeQuery(query);
	}
	
	/**
	 * set a friend relation to accepted
	 * @param user
	 * @param friend
	 */
	public void acceptFriend(User user, User friend) {
		String query = "INSERT INTO FriendsOf VALUES (NULL," + " '"
				+ user.getId() + "'," + " '" + friend.getId() + "',"
				+ " 'ACCEPTED')" + ";";
		sql.executeQuery(query);
		query = "UPDATE FriendsOf SET frdStatus='RESPONSE' WHERE frdUser1="
				+ friend.getId() + " AND frdUser2=" + user.getId() + ";";
		sql.executeQuery(query);
	}
	/**
	 * Save or update a user
	 * 
	 * @param user
	 */
	public void saveUser(User user) {
		if (user.getId() != 0)
			updateUser(user);
		else 
			saveNewUser(user);
	}
	
	/**
	 * Return an ArrayList of User that match the @criteria in the Username 
	 * and email fields.
	 * 
	 * @param criteria
	 * @return
	 */
	public ArrayList<User> searchUser(String criteria, User self) {
		String query = "SELECT * FROM User WHERE " +
				"(usrUsername LIKE '%" + criteria + "%' OR " +
				"usrEmail LIKE '%" + criteria + "') AND " +
				"usrID <> " + self.getId()+ ";";		
		ArrayList<User> result = new ArrayList<User>();
		ResultSet rs = sql.returnQuery(query);
		try {
			while (rs.next()) {
				User found = new User(rs.getInt("usrId"), rs.getString("usrUsername"), rs.getString("usrEmail"), rs.getString("usrStatus"));
				result.add(found);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
	/**
	 * Load all the friends of the selected user
	 * 
	 * @param user
	 */
	private ArrayList<User> loadFriends(User user) {
		String query = "SELECT * FROM FriendsOf A " +
				"INNER JOIN User B ON A.frdUser2 = B.usrID	" +
				"WHERE A.frdUser1 = " + user.getId() + " " +
				"ORDER BY frdStatus;";
		ResultSet rs = sql.returnQuery(query);
		ArrayList<User> friends=new ArrayList<User>();
		try {
			while (rs.next()) {
				User friend = new User();
				friend.setId(rs.getInt("usrID"));
				friend.setUsername(rs.getString("usrUsername"));
				friend.setEmail(rs.getString("usrEmail"));
				friend.setPassword(rs.getString("usrPassword"));
				friend.setStatus(rs.getString("usrStatus"));
				if (key.loadUserKey(friend.getUsername()) != null)
					friend.setPubKey(key.loadUserKey(friend.getUsername()));
				friends.add(friend);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return friends;
	}
	
	/**
	 * Create and returns a user from the ResultSet
	 * 
	 * @param rs
	 * @return
	 */
	private User buildUser(ResultSet rs) {
		User usr = new User();
		try {
			usr.setId(rs.getInt("usrID"));
			usr.setUsername(rs.getString("usrUsername"));
			usr.setEmail(rs.getString("usrEmail"));
			usr.setPassword(rs.getString("usrPassword"));
			usr.setStatus(rs.getString("usrStatus"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		PublicKey keyP = key.loadUserKey(usr.getUsername());
		if (keyP != null)
			usr.setPubKey(keyP);
		loadFriends(usr);
		return usr;
	}
	
	private void saveNewUser(User user) {
		String query = "INSERT INTO User VALUES(NULL, " +
				"	'" + user.getUsername() + "', " +
				"	'" + user.getEmail() + "', " +
				"	'" + user.getPassword() + "', " +
				"	'ACTIVE')";
		sql.executeQuery(query);
		key.storeUserKey(user.getUsername(), user.getPubKey());
		User loaded = loadByUsername(user.getUsername());
		user.setId(loaded.getId());
	}
	
	private void updateUser(User user) {
		String query = "UPDATE User SET " +
				"usrUsername = '" + user.getUsername() + "'," +
				"usrEmail = '" + user.getEmail() + "'," +
				"usrPassword = '" + user.getPassword() + "'," +
				"usrStatus = '" + user.getStatus() + "'" +
				"WHERE usrID = " + user.getId() + ";";
		sql.executeQuery(query);
		key.storeUserKey(user.getUsername(), user.getPubKey());
	}
	
	public boolean verify(String username, String password, boolean b) {
		// Remove old sessions
		String query = "DELETE FROM Session WHERE DATE_SUB(NOW(), INTERVAL " +
				"10 MINUTE) > ssnTime ";
		sql.executeQuery(query);
		// Check session for the user
		if (b) {
			query = "SELECT * " +
					"FROM Session INNER JOIN User ON ssnOwner = usrID WHERE usrUsername = '" + username +"';";
			ResultSet rs = sql.returnQuery(query);
			try {
				if (rs.next()) {
					return false;
				}
			} catch (SQLException e1) {
				e1.printStackTrace();
				return false;
			}
		}
		query = "SELECT usrUsername, usrPassword, usrStatus FROM User WHERE usrUsername='"
				+ username + "' AND usrPassword= '" + password + "' ;";
		ResultSet set=sql.returnQuery(query);
		try {
			if (!set.next()) 
				return false;
			else {
				String session = set.getString("usrStatus");
				if (session.equalsIgnoreCase("ACTIVE")) {
					ConfigManager.log("SESSION IS: " + session);
					return true;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * Add a new session to db and return the session id
	 * @param username
	 * @param sessionKey
	 * @return
	 */
	public int newSession(String username, String sessionKey) {
		int sessionid = 0;
		try {
			String query = "SELECT usrID FROM User WHERE usrUsername = '"
					+ username + "';";
			ResultSet set = sql.returnQuery(query);
			int userId = 0;
			set.first();
			userId = set.getInt("usrID");
			query = "INSERT INTO Session (ssnID, ssnKey, ssnOwner, ssnTime) VALUES (0, '"
					+ sessionKey + "', " + userId + ", NOW());";
			sql.executeQuery(query);
			query = "SELECT LAST_INSERT_ID();";
			set = sql.returnQuery(query);
			set.first();
			sessionid = set.getInt(1);
			ConfigManager.log("[USRMANAGER] New session, ID: " + sessionid);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return sessionid;
	}
	
	/**
	 * Return key corresponding to sessionid
	 * TODO forse c'è da aggiungere un controllo sul timeout, ma come si fa?
	 * @param ssnid
	 * @return
	 */
	public String loadSessionKey(int ssnid) {
		String query = "SELECT ssnKey FROM Session WHERE ssnID = " + ssnid;
		ResultSet rs = sql.returnQuery(query);
		String ssnKey = "";
		try {
			rs.first();
			ssnKey = rs.getString("ssnKey");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return ssnKey;
	}

	public ArrayList<User> getFriendsWhoAcceptMe(User user){
		String query = "SELECT * FROM User INNER JOIN " +
		"FriendsOf ON usrID = frdUser2 " +
		"WHERE (frdStatus = 'RESPONSE') AND " +
		"frdUser1 = " + user.getId() +";";
		ResultSet rs = sql.returnQuery(query);
		ArrayList<User> users = new ArrayList<User>();
		User u;
		try {
			while (rs.next()) {
				u = new User();
				u.setPubKey(key.loadUserKey(rs.getString("usrUsername")));
				u.setUsername(rs.getString("usrUsername"));
				users.add(u);
			}
		} catch (SQLException e) {		
			e.printStackTrace();
		}
		return users;
	}
	
	public ArrayList<User> getFriends(User user) {
		String query = "SELECT * FROM User INNER JOIN " +
				"FriendsOf ON usrID = frdUser2 " +
				"WHERE (frdStatus = 'ACCEPTED' OR frdStatus = 'RESPONSE') AND " +
				"frdUser1 = " + user.getId() + 
				" ORDER BY frdStatus;";
		ResultSet rs = sql.returnQuery(query);
		ArrayList<User> users = new ArrayList<User>();
		try {
			while (rs.next()) {
				ConfigManager.log(rs.getString("frdStatus"));
				users.add(new User(rs.getInt("usrId") , rs.getString("usrUsername"), rs.getString("usrEmail"), rs.getString("frdStatus")));
			}
		} catch (SQLException e) {		
			e.printStackTrace();
		}
		// Before return set RESPONSE status to ACCEPTED
		query = "UPDATE FriendsOf SET frdStatus = 'ACCEPTED' " +
				"WHERE frdUser1 = " + user.getId() + 
				" AND frdStatus='RESPONSE';";
		sql.executeQuery(query);
		return users;
	}
	
	public ArrayList<User> getFriendsRequestToMe(User user) {
		String query = "SELECT * FROM User INNER JOIN " +
				"FriendsOf ON usrID = frdUser1 " +
				"WHERE frdStatus = 'REQUEST' AND " +
				"frdUser2 = " + user.getId() +";";
		ResultSet rs = sql.returnQuery(query);
		ArrayList<User> users = new ArrayList<User>();
		try {
			while (rs.next())
				users.add(new User(rs.getInt("usrId") , rs.getString("usrUsername"), rs.getString("usrEmail"), rs.getString("frdStatus")));		
		} catch (SQLException e) {		
			e.printStackTrace();
		}
		return users;
	}
	
	public ArrayList<User> getFriendsRequestFromMe(User user) {
		String query = "SELECT * FROM User INNER JOIN " +
			"FriendsOf ON usrID = frdUser2 " +
			"WHERE frdStatus = 'REQUEST' AND " +
			"frdUser1 = " + user.getId() +";";
		ResultSet rs = sql.returnQuery(query);
		ArrayList<User> users = new ArrayList<User>();
		try {
			while (rs.next())
				users.add(new User(rs.getInt("usrId") , rs.getString("usrUsername"), rs.getString("usrEmail"), rs.getString("frdStatus")));		
		} catch (SQLException e) {		
			e.printStackTrace();
		}
		return users;
	}
	/**
	 * Check if sessionId is a valid session with a reasonable timeout
	 * TODO timeout in config?
	 * @param sessionId
	 * @return
	 */
	public boolean isSessionValid(int sessionId) {
		String query = "SELECT TIMESTAMPDIFF(MINUTE, ssnTime, NOW()) FROM Session WHERE TIMESTAMPDIFF(MINUTE, ssnTime, NOW()) < 1000 AND ssnID = " + sessionId + "; ";
		ResultSet rs = sql.returnQuery(query);
		try {
			if (rs.first()) {
				ConfigManager.log("Session is old: " + rs.getInt(1));
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
	
/**
 * Load user by a session id
 * TODO no error check necessary, I hope because we already check in session key
 * TODO caso d'errore possibile se l'utente si cancella mentre qualcuno ha provato a inviare
 * messaggi con lo stesso utente, è possibile?
 * @param sessionId
 * @return
 */
	public User loadSessionUser(int sessionId) {
		String query = "SELECT ssnOwner FROM Session WHERE ssnID = " + sessionId + ";";
		int usrId=0;
		ResultSet rs = sql.returnQuery(query);
		try {
			rs.first();
			usrId=rs.getInt("ssnOwner");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return loadByID(usrId);
	}

	/**
	 * Store key for user
	 * 
	 * @param username
	 * @param cryptedPriv
	 */
	public void storeUserPrivateKey(String username, String cryptedPriv) {
		key.storePrivateKey(username, cryptedPriv);

	}

	public String loadPrivateKey(String username) {
		return key.loadPrivateKey(username);
	}

	/**
	 * Force all accepted friends to RESPONSE status to force re-download of
	 * publickeys
	 * 
	 * @param username
	 */
	public void forceReloadFriendList(String username) {
		String query = "UPDATE FriendsOf SET frdStatus = 'RESPONSE' WHERE frdUser1 IN (SELECT usrID FROM User WHERE usrUsername=\""
				+ username + "\") AND frdStatus='ACCEPTED';";
		sql.executeQuery(query);
	}

	/**
	 * Restore accepted friends, after a forced download
	 * 
	 * @param user
	 */
	public void restoreAcceptedFriends(User user) {
		String query = "UPDATE FriendsOf SET frdStatus = 'ACCEPTED' WHERE frdUser1="
				+ user.getId() + " AND frdStatus='RESPONSE';";
		sql.executeQuery(query);

	}
	
	public void logoutUser(User user) {
		String query ="DELETE FROM Session WHERE ssnOwner = " + user.getId() + ";";
		sql.executeQuery(query);
	}
	
	public void updateSessionTime(int userID) {
		String query = "UPDATE Session SET  ssnTime = NOW() WHERE ssnOwner = " + userID;
		sql.executeQuery(query);
	}
}